//
//  SEGBasicDataBase.m
//  SEGBasic
//
//  Created by 刘山 on 2018/3/15.
//  Copyright © 2018年 刘山. All rights reserved.
//


#import "SEGBasicDataBase.h"
@implementation SEGBasicDataBase

/*
 NSDocumentDirectory：Documents：应用中用户数据可以放在这里，iTunes备份和恢复的时候会包括此目录
 NSCachesDirectory：Library/Caches：存放缓存文件，iTunes不会备份此目录，此目录下文件不会在应用退出删除
 */

+ (NSString*)filePath:(NSString*)name
{
    return [[NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:[NSString stringWithFormat:@"%@.sqlite", name]];
}

+ (void)createDBWithDBName:(NSString*)dbName
{
    // 2.创建队列
    _queue = [FMDatabaseQueue databaseQueueWithPath:[self filePath:dbName]];
}

+ (BOOL)createTableWithDBName:(NSString*)aName WithTable:(NSString*)table
{
    __block BOOL _isCreateTable = NO;
    
    [self createDBWithDBName:aName];
    
    // 3.创表
    [_queue inDatabase:^(FMDatabase* db) { // 这句代码内部包含了创建数据库实例
        
        if (db.open) {
            _isCreateTable = [db executeUpdate:table];
        }
        [db close];
        
    }];
    
    [_queue close];
    
    return _isCreateTable;
}

+ (BOOL)executeDB:(NSString*)aName WithSQL:(NSString*)sql OfValues:(NSArray*)values
{
    
    __block BOOL _isExecute = NO;
    
    [self createDBWithDBName:aName];
    
    [_queue inDatabase:^(FMDatabase* db) {
        
        if (db.open) {
            _isExecute = [db executeUpdate:sql withArgumentsInArray:values];
        }
        [db close];
    }];
    
    [_queue close];
    
    return _isExecute;
}

+ (BOOL)insertRowWithDBName:(NSString*)aName tableName:(NSString*)table ArgumentsDict:(NSDictionary*)dict
{
    __block BOOL _isInsert = NO;
    
    [self createDBWithDBName:aName];
    
    if (!_queue) {
        return _isInsert;
    };
    
    __block NSString* value = [NSString string];
    
    [dict enumerateKeysAndObjectsUsingBlock:^(id _Nonnull key, id _Nonnull obj, BOOL* _Nonnull stop) {
        value = [value stringByAppendingString:[NSString stringWithFormat:@":%@,", key]];
    }];
    
    if (value && value.length > 0) {
        value = [value substringToIndex:value.length - 1];
    }
    
    value = [NSString stringWithFormat:@"INSERT INTO %@ VALUES(%@)", table, value];
    
    [_queue inDatabase:^(FMDatabase* db) {
        
        _isInsert = [db executeUpdate:value withParameterDictionary:dict];
        
        [db executeUpdateWithFormat:@"inset%@", @""];
        
    }];
    
    [_queue close];
    
    return _isInsert;
}

+ (NSArray*)searchDB:(NSString*)aName WithTable:(NSString*)table ForHeaders:(NSArray*)headers Conditions:(NSArray*)conditons
{
    
    NSArray* result = nil;
    
    if (![self createTableWithDBName:aName WithTable:table]) {
        return result;
    }
    
    __block FMResultSet* rs = nil;
    
    __block NSString* sql = @"*";
    
    if (headers) {
        [headers enumerateObjectsUsingBlock:^(id _Nonnull obj, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = [sql stringByAppendingString:[NSString stringWithFormat:@"%@,", obj]];
        }];
        sql = [sql substringToIndex:sql.length - 1];
    }
    
    [_queue inDatabase:^(FMDatabase* db) {
        rs = [db executeQuery:sql values:conditons error:nil];
        
    }];
    
    NSMutableArray* _arrM = [NSMutableArray arrayWithCapacity:headers.count];
    
    if (rs.next) {
        
        __block NSMutableDictionary* _dic = [NSMutableDictionary dictionary];
        
        if (headers) {
            [headers enumerateObjectsUsingBlock:^(id _Nonnull obj, NSUInteger idx, BOOL* _Nonnull stop) {
                
                [_dic setObject:[rs dataForColumn:[NSString stringWithFormat:@"%@", obj]] forKey:[NSString stringWithFormat:@"%@", obj]];
            }];
        }
        
        [_arrM addObject:_dic];
    }
    
    result = _arrM;
    
    [_queue close];
    
    return result;
}

+ (NSArray*)executeQueryWithDBName:(NSString*)aName WithSQL:(NSString*)sql OfValues:(NSArray*)values
{
    NSArray* _result = [NSArray array];
    
    [self createDBWithDBName:aName];
    
    if (!_queue) {
        return nil;
    }
    
    __block FMResultSet* _rs;
    
    __block NSMutableArray* _arrM = [NSMutableArray array];
    
    if (values.count == 0) {
        
        [_queue inDatabase:^(FMDatabase* db) {
            
            _rs = [db executeQuery:sql];
            
            while ([_rs next]) {
                
                NSDictionary* _dic = _rs.resultDictionary;
                
                [_arrM addObject:_dic];
            }
            
            [_rs close];
            
        }];
    }
    else {
        
        [_queue inDatabase:^(FMDatabase* db) {
            
            _rs = [db executeQuery:sql withArgumentsInArray:values];
            
            while ([_rs next]) {
                
                NSDictionary* _dic = _rs.resultDictionary;
                
                [_arrM addObject:_dic];
            }
            
            [_rs close];
            
        }];
    }
    
    [_queue close];
    
    _result = _arrM;
    
    return _result;
}

//判断表是否存在
+ (BOOL)hasTableInDB:(FMDatabase*)db WithTableName:(NSString*)tableName
{
    tableName = [tableName lowercaseString];
    
    FMResultSet* rs = [db executeQuery:@"select [sql] from sqlite_master where [type] = 'table' and lower(name) = ?", tableName];
    
    //if at least one next exists, table exists
    BOOL returnBool = [rs next];
    
    //close and free object
    [rs close];
    
    return returnBool;
}

//判断字段是否存在
+ (BOOL)hasColumName:(NSString*)columnName InTable:(NSString*)tableName InDataBase:(FMDatabase*)db
{
    BOOL returnBool = NO;
    
    tableName = [tableName lowercaseString];
    columnName = [columnName lowercaseString];
    
    FMResultSet* rs = [self getTableSchema:tableName InDataBase:db];
    
    while ([rs next]) {
        if ([[[rs stringForColumn:@"name"] lowercaseString] isEqualToString:columnName]) {
            returnBool = YES;
            break;
        }
    }
    
    [rs close];
    
    return returnBool;
}

+ (FMResultSet*)getTableSchema:(NSString*)tableName InDataBase:(FMDatabase*)db
{
    
    FMResultSet* rs = [db executeQuery:[NSString stringWithFormat:@"pragma table_info('%@')", tableName]];
    
    return rs;
}

/*---------------------------------------------------SQLite数据库升级--------------------------------------------------*/
/*
 数据库升级需要注意的地方：为了防止错误－为关键语句添加脚本文件
 1.表子段的增、删。
 2.如果子段不存在，对子段进行操作会不会出现闪退情况？
 */

+ (BOOL)updateDatabaseWithDB:(NSString*)dbName TableName:(NSString*)tableName ColumnName:(NSString*)columnName
{
    
    _database = [FMDatabase databaseWithPath:[self filePath:dbName]];
    
    if (!_database.open) {
        return NO;
    }
    
    if (![self hasTableInDB:_database WithTableName:tableName]) {
        return NO;
    }
    
    if ([self hasColumName:columnName InTable:tableName InDataBase:_database]) {
        return NO;
    }
    
    NSString* alerTable = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER", tableName, columnName];
    
    return [_database executeUpdate:alerTable];
}
/*----------------------------------------------------------------------------------------------------------------------*/

@end
